package com.interfaces;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.entity.CustomerMeter;
import com.entity.CustomerMeterPK;

/**
 * @Title: CustomerMeterDao.java
 * @Package com.interfaces
 * @author 孙梦楠
 * @date 2017年7月31日 上午10:20:41
 * @version V1.0.0
 */

public interface CustomerMeterDao extends JpaRepository<CustomerMeter, CustomerMeterPK> {

	// 根据用户ID列表
	@Transactional
	@Query("SELECT cm.pk FROM CustomerMeter cm WHERE cm.pk.customerid = ?1")
	List<CustomerMeter> findByCustomerid(Integer customerid);
	
	@Transactional
	@Query("SELECT cm FROM CustomerMeter cm WHERE cm.pk.customerid = ?1")
	CustomerMeter findByCustomerid2(Integer customerid);
	
	
	//根据用户ID删除
	@Transactional
	
	@Query("DELETE  FROM CustomerMeter cm WHERE cm.pk.customerid = ?1")
	CustomerMeter deleteByCustomerid(Integer customerid);

	// 根据表ID列表
	@Transactional
	
	@Query("SELECT cm FROM CustomerMeter cm WHERE cm.pk.meterid = ?1")
	CustomerMeter findByMeterid(Integer meterid);
	
	//根据表ID删除
	@Transactional
	@Modifying
	@Query("DELETE  FROM CustomerMeter cm WHERE cm.pk.meterid = ?1")
	public void deleteByMeterid(Integer meterid);

	// 条件列表
	@Transactional

	@Query("SELECT cm.pk FROM CustomerMeter cm WHERE (cm.pk.customerid = ?1 OR ?1 IS NULL ) "
			+ "AND (cm.pk.meterid = ?2  OR ?2 IS NULL ) "
			+ "AND (cm.pk.customerid IN (SELECT c FROM Customer c WHERE c.customerorg=?3) )")
	List<CustomerMeter> findByConditions(Integer customerid, Integer meterid, Integer customerorg);

	// 根据双主键查询
	@Transactional

	@Query("SELECT cm.pk FROM CustomerMeter cm WHERE cm.pk.customerid = ?1 AND cm.pk.meterid = ?2")
	CustomerMeter findByCustomeridAndMeterid(Integer customerid, Integer meterid);

	// 根据用户org获取所有列表
	@Transactional
	@Query(value = "SELECT cm.pk FROM CustomerMeter cm WHERE cm.pk.customerid IN (SELECT c FROM Customer c WHERE c.customerorg=?1 AND c.customertype=?2) ")
	public List<CustomerMeter> findByOrg(Integer customerorg,String customertype);

	// 根据用户org获取所有列表
	@Transactional
	@Query(value = "SELECT cm.pk FROM CustomerMeter cm WHERE cm.pk.customerid IN (SELECT c FROM Customer c WHERE (c.customerorg=?1) "
			+ "AND (c.customeraccount=?2 OR ?2 IS NULL ) AND (c.customername=?3 OR ?3 IS NULL ) "
			+ "AND (c.customeraddress=?4 OR ?4 IS NULL)  AND c.customertype=?5) ")
	public List<CustomerMeter> findByCustomerConditions(Integer customerorg, String customeraccount,
			String customername, String customeraddress,String customertype);

}
